# 麦园垃圾站一卡通2.28导个性定制月度部门报表程序V202112

import time
import pyodbc
import pandas as pd
import xlsxwriter
import threading

# 注:嵌套的字典容器结构如下:{'col1':{'day1':'data1','day2':'data2',....},'col2':{'day1':'data1','day2':'data2',...}.....}

def baobiao(deptlist):
    bb_dict = {}  # 总表字典容器,作用:临时储存数据,并对数据进行效验,格式化重排以便更加便捷地构建df
    meallist = ['早餐', '午餐', '晚餐', '夜宵']
    moneytypelist = ['次数', '金额']
    s_null = '-'

    # 生成日期和机号列
    def search_day():
        bb_dict['日期'] = {}
        with pyodbc.connect(DRIVER='{SQL Server}', SERVER='(local)', DATABASE='ICCMDB2.0', UID='sa',
                            PWD='000000') as db:
            cursor = db.cursor()
            sql_day = f''' SELECT DISTINCT CONVERT(VARCHAR,fCons_Time,23) 日期 FROM [ICCMDB2.0].[dbo].[T_C_RecC_{search_time}] ORDER BY 日期 '''
            cursor.execute(sql_day)
            csdata = cursor.fetchall()
            for row in csdata:
                bb_dict['日期'][row[0]] = '1'

    # 查询部门月每天的(早餐,午餐,晚餐,夜宵)餐次数(绿源,洪源,环保)
    def search_cs(dept, meal):
        bb_dict[f'{dept}|{meal}|次数'] = {}
        with pyodbc.connect(DRIVER='{SQL Server}', SERVER='(local)', DATABASE='ICCMDB2.0', UID='sa',
                            PWD='000000') as db:
            cursor = db.cursor()
            sql_chishu = f''' SELECT llite.日期,COUNT(llite.交易额) 次数 FROM
    (SELECT CONVERT(VARCHAR,lsb.fCons_Time,23) 日期  ,ccb.fMeal_Name 餐次 ,lsb.fCons_Fare 交易额 ,dpb.fDeptName 部门
    FROM [ICCMDB2.0].[dbo].[T_C_RecS_{search_time}] lsb,[ICCMDB2.0].[dbo].[T_B_Employee] eeb,[ICCMDB2.0].[dbo].[T_B_Dept] dpb,[ICCMDB2.0].[dbo].[T_B_MealSet] ccb
    WHERE lsb.fAccount_id = eeb.fAccountID AND lsb.fDp_bh = dpb.fDeptCode AND lsb.fCons_State = 0 AND lsb.fPos_id = 1 AND lsb.fMeal_id = ccb.fMeal_Id
    ) llite
    WHERE llite.部门 = '{dept}' AND llite.餐次 = '{meal}'
    group by llite.日期 '''
            cursor.execute(sql_chishu)
            csdata = cursor.fetchall()
            for item in bb_dict['日期']:
                bb_dict[f'{dept}|{meal}|次数'][item] = s_null  # 初始化充容器
            for row in csdata:
                bb_dict[f'{dept}|{meal}|次数'][row[0]] = row[1]  # 数据填充

    # 查询部门月每天的(早餐,午餐,晚餐,夜宵)餐次金额(绿源,洪源,环保)
    def search_money(dept, meal):
        bb_dict[f'{dept}|{meal}|金额'] = {}
        with pyodbc.connect(DRIVER='{SQL Server}', SERVER='(local)', DATABASE='ICCMDB2.0', UID='sa',
                            PWD='000000') as db:
            cursor = db.cursor()
            sql_money = f''' SELECT llite.日期,SUM(llite.交易额) 金额 FROM
    (SELECT CONVERT(VARCHAR,lsb.fCons_Time,23) 日期  ,ccb.fMeal_Name 餐次 ,lsb.fCons_Fare 交易额 ,dpb.fDeptName 部门
    FROM [ICCMDB2.0].[dbo].[T_C_RecS_{search_time}] lsb,[ICCMDB2.0].[dbo].[T_B_Employee] eeb,[ICCMDB2.0].[dbo].[T_B_Dept] dpb,[ICCMDB2.0].[dbo].[T_B_MealSet] ccb
    WHERE lsb.fAccount_id = eeb.fAccountID AND lsb.fDp_bh = dpb.fDeptCode AND lsb.fCons_State = 0 AND lsb.fPos_id = 1 AND lsb.fMeal_id = ccb.fMeal_Id
    ) llite
    WHERE llite.部门 = '{dept}' AND llite.餐次 = '{meal}'
    group by llite.日期 '''
            cursor.execute(sql_money)
            moneydata = cursor.fetchall()
            for item in bb_dict['日期']:
                bb_dict[f'{dept}|{meal}|金额'][item] = s_null  # 初始化充容器
            for row in moneydata:
                bb_dict[f'{dept}|{meal}|金额'][row[0]] = row[1]  # 数据填充

    # 查询部门月每天的餐次次数汇总(绿源,洪源,环保)
    def search_zcs(dept):
        bb_dict[f'{dept}|总次数'] = {}
        with pyodbc.connect(DRIVER='{SQL Server}', SERVER='(local)', DATABASE='ICCMDB2.0', UID='sa',
                            PWD='000000') as db:
            cursor = db.cursor()
            sql_zchishu = f''' SELECT llite.日期,COUNT(llite.交易额) 次数 FROM
    (SELECT CONVERT(VARCHAR,lsb.fCons_Time,23) 日期  ,ccb.fMeal_Name 餐次 ,lsb.fCons_Fare 交易额 ,dpb.fDeptName 部门
    FROM [ICCMDB2.0].[dbo].[T_C_RecS_{search_time}] lsb,[ICCMDB2.0].[dbo].[T_B_Employee] eeb,[ICCMDB2.0].[dbo].[T_B_Dept] dpb,[ICCMDB2.0].[dbo].[T_B_MealSet] ccb
    WHERE lsb.fAccount_id = eeb.fAccountID AND lsb.fDp_bh = dpb.fDeptCode AND lsb.fCons_State = 0 AND lsb.fPos_id = 1 AND lsb.fMeal_id = ccb.fMeal_Id
    ) llite
    WHERE llite.部门 = '{dept}'
    group by llite.日期 '''
            cursor.execute(sql_zchishu)
            csdata = cursor.fetchall()
            for item in bb_dict['日期']:
                bb_dict[f'{dept}|总次数'][item] = s_null  # 初始化充容器
            for row in csdata:
                bb_dict[f'{dept}|总次数'][row[0]] = row[1]  # 数据填充

    # 查询部门月每天的餐次金额汇总(绿源,洪源,环保)
    def search_zmoney(dept):
        bb_dict[f'{dept}|总金额'] = {}
        with pyodbc.connect(DRIVER='{SQL Server}', SERVER='(local)', DATABASE='ICCMDB2.0', UID='sa',
                            PWD='000000') as db:
            cursor = db.cursor()
            sql_zchishu = f''' SELECT llite.日期,SUM(llite.交易额) 次数 FROM
    (SELECT CONVERT(VARCHAR,lsb.fCons_Time,23) 日期  ,ccb.fMeal_Name 餐次 ,lsb.fCons_Fare 交易额 ,dpb.fDeptName 部门
    FROM [ICCMDB2.0].[dbo].[T_C_RecS_{search_time}] lsb,[ICCMDB2.0].[dbo].[T_B_Employee] eeb,[ICCMDB2.0].[dbo].[T_B_Dept] dpb,[ICCMDB2.0].[dbo].[T_B_MealSet] ccb
    WHERE lsb.fAccount_id = eeb.fAccountID AND lsb.fDp_bh = dpb.fDeptCode AND lsb.fCons_State = 0 AND lsb.fPos_id = 1 AND lsb.fMeal_id = ccb.fMeal_Id
    ) llite
    WHERE llite.部门 = '{dept}'
    group by llite.日期 '''
            cursor.execute(sql_zchishu)
            csdata = cursor.fetchall()
            for item in bb_dict['日期']:
                bb_dict[f'{dept}|总金额'][item] = s_null  # 初始化充容器
            for row in csdata:
                bb_dict[f'{dept}|总金额'][row[0]] = row[1]  # 数据填充

    # 将上述数据查找获取后添加至字典容器以便依次排序取出呈现,并且添加数据效验格式重排,数据效验是为了避免空值导致错序导致数据失真BUG
    def add_to_bb_dic():
        search_day()
        for dp in deptlist:
            for ml in meallist:
                search_cs(dp, ml)
                search_money(dp, ml)
            search_zcs(dp)
            search_zmoney(dp)

    # 从数据容器导出报表
    def bb_dic_to_excel():
        for dp in deptlist:
            df_temp = None
            df_temp = pd.DataFrame(
                {'日期': bb_dict['日期'].keys(), '机号': bb_dict['日期'].values()})  # 先创建日期和机号列,让其自动生成df容器并且生成索引
            for ml in meallist:
                for mtp in moneytypelist:
                    df_temp[f'{ml}{mtp}'] = bb_dict[f'{dp}|{ml}|{mtp}'].values()
            df_temp['总次数'] = bb_dict[f'{dp}|总次数'].values()
            df_temp['总金额'] = bb_dict[f'{dp}|总金额'].values()

            copy_df_temp = df_temp.copy(deep=True)  # 深度复制部门报表(主要是为了得到列名),添加两行单元格做报头然后剩下的依次复制原表数据
            copy_df_temp.loc[0] = None  # 手动构建第一列(方便合并单元格)
            copy_df_temp.loc[1] = list(copy_df_temp.columns)  # 手动构建第一列(列名)
            for xrow in range(len(df_temp.index)):
                for ycol in list(copy_df_temp.columns):
                    copy_df_temp.loc[xrow + 2, ycol] = df_temp.loc[xrow, ycol]
            ############################################################################
            # 格式化样式并输出EXCEL (使用前要先安装xlsxwriter库)
            writer = pd.ExcelWriter(f'{dp}{search_time}月度报表.xlsx', engine='xlsxwriter')
            workbook = writer.book
            copy_df_temp.to_excel(writer, sheet_name='Sheet1', index=None)
            worksheet = writer.sheets['Sheet1']
            format = workbook.add_format()
            format.set_align('center')  # 水平居中
            format.set_align('vcenter')  # 垂直居中
            worksheet.set_column('A:L', 12, format)  # 需要格式化样式的列,列长度
            worksheet.merge_range('A1:L2', f'{dp}{search_time}月度报表')  # 合并单元格
            writer.save()
            ############################################################################
    add_to_bb_dic()  # 数据乱序存入字典容器,其实也可以不要,即直接在循环中写入pandas的df表中,但是我们为了直观和后期维护,选择了做下数据中转
    bb_dic_to_excel()  # 从字典容器依次取出数据生成三张报表


if __name__ == '__main__':
    print('请输入要导出的月份,然后按[Enter]键开始,例如要导出2022年1月报表,输入202201即可')
    search_time = input('请输入月份:')

    task=[]
    for i in [['绿源'],['洪源'],['环保']]:
        t=threading.Thread(target=baobiao,args=(i,))
        task.append(t)
        t.start()
        print(f'正在生成 {i} 报表!')

    for t in task:
        t.join()

    print('报表已生成!')
    for i in range(3,-1,-1):
        print(f'程序即将自动退出!   {i}s')
        time.sleep(1)

